package com.huayue.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.RoomRealOrder;
import com.huayue.entity.RoomRealOrderDetails;
import com.huayue.util.DateHelper;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 入住单
 */
@Component
public class RoomRealOrderDao implements BaseDao<RoomRealOrder> {
    @Override
    public int insert(RoomRealOrder entity) {

        String sql =
            "INSERT INTO room_real_order (ordernumber, customer_id, employee_id, totalprice, payment, person_number, "
                + " remark, real_in_time, real_out_time,days) VALUES (?, (select max(id) from customer), ?, ?, ?, ?, ?, ?, ?,?);";
        return DBHelper.executeUpdate(sql, entity.getOrdernumber(), entity.getEmployeeId(), entity.getTotalprice(),
            entity.getPayment(), entity.getPersonNumber(), entity.getRemark(), entity.getRealInTime(),
            entity.getRealOutTime(), entity.getDays());
    }

    @Override
    public int update(RoomRealOrder entity) {
        return 0;
    }

    @Override
    public int delete(RoomRealOrder entity) {
        /**
         * @Author: 陈世纪
         * @Date: 1:41 2020/3/10
         * @Description: 删除入住订单时，修改room_real_order_details，room_deposit两表中的is_deleted =1 ，room_status
         *               表中的status_id,为1--空房
         */

        DBHelper.executeUpdate("update room_real_order_details set is_deleted = 1 where room_real_order_id=?",
            entity.getId());
        DBHelper.executeUpdate("update room_deposit set is_deleted = 1 where room_real_order_id = ?", entity.getId());
        List<Map<String, Object>> list =
            DBHelper.executeQuery("select * from room_deposit where room_real_order_id=?", entity.getId());
        for (int i = 0; i < list.size(); i++) {
            DBHelper.executeUpdate("update room_status set status_id = 1 where room_id = ?",
                list.get(i).get("room_id"));
        }

        String sql = "update room_real_order set is_deleted =1 where id=?";
        return DBHelper.executeUpdate(sql, entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        String sql = "select * from room_real_order where is_deleted = 0";
        return DBHelper.executeQuery(sql);
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        RoomRealOrder room_real_order = (RoomRealOrder)obj[0];
        if (room_real_order.getIsStatus() == 9) {
            String sql =
                "select rro.ordernumber,r.name,rrod.gmt_create real_in_time,rrod.gmt_modified real_out_time,e.realname ename,c.realname realname,c.idnumber idnumber from room_real_order_details rrod\n"
                    + "left join room_real_order rro on rro.id=rrod.room_real_order_id\n"
                    + "left join customer c on c.id=rrod.customer_id left join room r on r.id = rrod.room_id\n"
                    + "left join employee e on rro.employee_id=e.id where rrod.is_status=9  ";
            sql += "  order by rro.id desc limit ?,? ";
            return DBHelper.executeQuery(sql, page.getBegin(), page.getPageSize());
        }
        String sql =
            " select rro.ordernumber,r.name,rrod.gmt_create real_in_time,rs.out_time real_out_time,e.realname ename,c.realname realname,c.idnumber idnumber from room_real_order_details rrod\n"
                + " left join room_real_order rro on rro.id=rrod.room_real_order_id\n"
                + " left join customer c on c.id=rrod.customer_id left join room r on r.id = rrod.room_id\n"
                + " left join room_status rs on rrod.room_id=rs.room_id "
                + " left join employee e on rro.employee_id=e.id where (rrod.is_status=0 or  rrod.is_status=2) ";

        if (room_real_order.getOrdernumber() != null && !"".equals(room_real_order.getOrdernumber())) {
            String str = room_real_order.getOrdernumber();
            sql += " and (c.realname like '%" + str + "%' or e.realname like '%" + str + "%' or rro.ordernumber like '%"
                + str + "%')";
        }
        sql += "  order by rrod.id desc limit ?,? ";
        return DBHelper.executeQuery(sql, page.getBegin(), page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(RoomRealOrder entity) {
        String sql =
            " select rro.*,rd.price dprice,e.id eid,e.realname ename,c.realname,c.phone,c.sex,c.idnumber from room_real_order rro \n"
                + " left join employee e on rro.employee_id = e.id left join customer c on c.id=rro.customer_id\n"
                + " left join room_deposit rd on rd.room_real_order_id=rro.id\n"
                + " where rro.id = ? and rro.is_deleted=0 ";
        return DBHelper.executeQuery(sql, entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        RoomRealOrder room_real_order = (RoomRealOrder)obj[0];
        if (room_real_order.getIsStatus() == 9) {
            String sql = "select count(*) counts from room_real_order_details rrod\n"
                + "left join room_real_order rro on rro.id=rrod.room_real_order_id\n"
                + "left join customer c on c.id=rrod.customer_id left join room r on r.id = rrod.room_id\n"
                + "left join employee e on rro.employee_id=e.id where rrod.is_status=9  ";
            sql += "  order by rro.id desc  ";
            // 避免查询到总数据条数为0时报错
            List<Map<String, Object>> list = DBHelper.executeQuery(sql);
            if (list.size() == 0) {
                return 0;
            }
            return Integer.valueOf(list.get(0).get("counts").toString());
        }

        String sql = " select count(*) counts from room_real_order_details rrod\n"
            + " left join room_real_order rro on rro.id=rrod.room_real_order_id\n"
            + " left join customer c on c.id=rrod.customer_id left join room r on r.id = rrod.room_id\n"
            + " left join employee e on rro.employee_id=e.id where (rrod.is_status=0 or  rrod.is_status=2) ";

        if (room_real_order.getOrdernumber() != null && !"".equals(room_real_order.getOrdernumber())) {
            String str = room_real_order.getOrdernumber();
            sql += " and (c.realname like '%" + str + "%' or e.realname like '%" + str + "%' or rro.ordernumber like '%"
                + str + "%')";
        }
        // 避免查询到总数据条数为0时报错
        List<Map<String, Object>> list = DBHelper.executeQuery(sql);
        if (list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("counts").toString());
    }

    // 查找最大id数
    public int findMaxId() {
        return Integer
            .valueOf(DBHelper.executeQuery("select max(id) maxid from room_real_order").get(0).get("maxid").toString());
    }

    // 修改状态is_status

    public int changeIs_status(RoomRealOrder room_real_order) {
        String sql = "update room_real_order set is_status =? where id = ?";
        return DBHelper.executeUpdate(sql, room_real_order.getIsStatus(), room_real_order.getId());
    }

    // 查询可用房间
    public List<Map<String, Object>> findNullfrom(Object... obj) {
        String sql = "select rt.id rtid,rt.name rtname,r.name,r.id,rs.status_id,rt.price from room_type rt\n"
            + " left join room r on rt.id = r.room_type_id left join room_status rs on rs.room_id=r.id\n"
            + " where rs.status_id=1 and r.is_deleted=0";
        return DBHelper.executeQuery(sql);
    }

    // 入住成功后，修改room_status表中的status_Id字段
    public int updateRoom_status(RoomRealOrder roomRealOrder, RoomRealOrderDetails details) {
        String sql =
            "update room_status set customer_id=(select max(id) from customer),status_id = 2,in_time=?,out_time=? where room_id = ?";
        return DBHelper.executeUpdate(sql, roomRealOrder.getRealInTime(), roomRealOrder.getRealOutTime(),
            details.getRoom_id());
    }

    // 查询可用房间
    public List<Map<String, Object>> findfrom(Object... obj) {
        String sql = "select rt.id rtid,rt.name rtname,r.name,r.id,rs.status_id,rt.price from room_type rt\n"
            + " left join room r on rt.id = r.room_type_id left join room_status rs on rs.room_id=r.id\n"
            + " where r.is_deleted=0";
        return DBHelper.executeQuery(sql);
    }

    /**
     * @Author: 陈世纪
     * @Date: 1:06 2020/3/10
     * @Description: 查看详情时，room房间的押金 --- 暂时没有用上
     */
    public List<Map<String, Object>> findRoom_deposit_price(String id) {
        String sql = "select MAX(rd.price) rdprice from room_real_order rro  "
            + "inner join room_deposit rd on rro.id = rd.room_real_order_id  "
            + "where rro.is_deleted = 0 and rro.id = ? ";
        return DBHelper.executeQuery(sql, id);
    }

    public RoomRealOrder findDaysAndInTimeByRoomId(Integer roomId) {
        String sql = "select rro.real_in_time,rro.days from room_real_order rro\n"
            + "right join room_real_order_details rrod on rrod.room_real_order_id=rro.id where rrod.room_id=?";
        List<Map<String, Object>> list = DBHelper.executeQuery(sql, roomId);
        RoomRealOrder order = new RoomRealOrder();
        order.setRealInTime(DateHelper.StringToDateShort(list.get(0).get("real_in_time").toString()));
        order.setDays(Long.valueOf(list.get(0).get("days").toString()));
        return order;
    }

    public Map<String, Object> findRoomRenew(Integer roomId) {
        // 查询原房主信息
        String sql = "select rs.out_time real_out_time,rrod.price,c.realname from room_real_order rro\n"
            + "right join room_real_order_details rrod on rrod.room_real_order_id=rro.id\n"
            + "left join room_type rt on rrod.room_type_id=rt.id "
            + " left join room_status rs on rs.room_id=rrod.room_id  left join customer c on rrod.customer_id=c.id "
            + " where rrod.room_id=" + roomId + " order by rrod.id desc limit 0,1";
        Map<String, Object> info = DBHelper.executeQuery(sql).get(0);
        // 查询可续住的天数
        String sqls = "select rpo.predict_in_time from room_predict_order rpo\n"
            + "RIGHT JOIN room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
            + "where rpod.is_status=0\n" + "and rpod.room_id = " + roomId;
        List<Map<String, Object>> list = DBHelper.executeQuery(sqls);
        if (list.size() == 0) {
            info.put("days", 30);
        } else {
            int days = DateHelper.getDateRangeNumber(DateHelper.StringToDate(info.get("real_out_time").toString()),
                DateHelper.StringToDate(list.get(0).get("predict_in_time").toString()));
            info.put("days", days);
        }

        return info;
    }
}
